const express = require('express')
const router = express.Router()
const dbManager = require('./db')
const sql = require('mssql')

// 测试路由
router.get('/test', (req, res) => {
  res.json({ message: 'API is working' })
})

// 获取数据库状态
router.get('/status/:dbName', (req, res) => {
  const { dbName } = req.params
  res.json({
    success: true,
    state: dbManager.getDbStatus(dbName)
  })
})

// 连接数据库
router.post('/connect/:dbName', async (req, res) => {
  const { dbName } = req.params
  try {
    await dbManager.createPool(dbName)
    res.json({
      success: true,
      message: `数据库 ${dbName} 连接成功`
    })
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    })
  }
})

// 执行查询
router.post('/query/:dbName', async (req, res) => {
  const { dbName } = req.params
  try {
    const { sql: querySQL, params } = req.body
    const pool = await dbManager.getPool(dbName)
    const request = pool.request()

    if (params && typeof params === 'object') {
      Object.entries(params).forEach(([key, value]) => {
        const paramName = key === '工单号' ? 'workOrderId' : key.toLowerCase()
        request.input(paramName, value)
      })
    }

    const result = await request.query(querySQL)

    const formattedData = result.recordset.map(row => {
      const formattedRow = { ...row }
      for (let key in formattedRow) {
        if (formattedRow[key] instanceof Date) {
          formattedRow[key] = formattedRow[key].toLocaleString()
        }
      }
      return formattedRow
    })

    res.json({
      success: true,
      data: formattedData,
      total: result.recordset.length
    })
  } catch (error) {
    console.error('查询执行失败:', error)
    res.status(500).json({
      success: false,
      error: error.message
    })
  }
})

// 颜色查询接口
router.get('/db/selectColor/:workOrderId', async (req, res) => {
  try {
    const { workOrderId } = req.params;
    const pool = await dbManager.getPool('mesdb');
    const request = pool.request();

    const query = `
      SELECT 
        COLOUR,
        LOT_ID,
        CREATED,
        PARENT_LOT_RRN,
        WO_ID,
        EFF
      FROM dbo.wip_lot 
      WHERE WO_ID = @workOrderId 
        AND COLOUR <> 'DA' 
        AND LOT_ID LIKE '%86%' 
        AND PARENT_LOT_RRN IS NULL 
      ORDER BY CREATED
    `;

    request.input('workOrderId', sql.NVarChar, workOrderId);
    const result = await request.query(query);

    res.json({
      success: true,
      data: {
        list: result.recordset,
        total: result.recordset.length
      }
    });

  } catch (error) {
    console.error('颜色查询失败:', error);
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// 修改颜色更新接口
router.post('/db/updateColor/:woId', async (req, res) => {
  try {
    const { woId } = req.params;
    const pool = await dbManager.getPool('mesdb');
    const request = pool.request();
    
    const query = `
      UPDATE dbo.wip_lot 
      SET COLOUR = 'DA'
      WHERE WO_ID = @woId
        AND LOT_ID LIKE '%86%' 
        AND COLOUR <> 'DA'
        AND PARENT_LOT_RRN IS NULL
    `;

    request.input('woId', sql.NVarChar, woId);
    await request.query(query);
    
    res.json({
      success: true,
      message: '更新成功'
    });

  } catch (error) {
    console.error('颜色更新失败:', error);
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// 组件玻璃查询接口
router.get('/db/selectGlass/:lotId', async (req, res) => {
  try {
    const { lotId } = req.params;
    const pool = await dbManager.getPool('mesdb');
    const request = pool.request();

    // 修改查询语句，添加 schema
    const query = `
      SELECT * FROM WIP_GLASS WHERE LOT_ID= @lotId
    `;

    console.log('查询参数:', lotId);

    request.input('lotId', sql.NVarChar, lotId);

    // 添加调试日志
    console.log('执行查询:', query, '参数:', lotId);

    const result = await request.query(query);

    console.log('查询结果:', result);

    if (!result.recordset || result.recordset.length === 0) {
      return res.json({
        success: true,
        data: {
          list: [],
          total: 0,
          message: '未找到匹配的记录'
        }
      });
    }

    const formattedData = result.recordset.map(row => {
      const formattedRow = { ...row };
      for (let key in formattedRow) {
        if (formattedRow[key] instanceof Date) {
          formattedRow[key] = formattedRow[key].toLocaleString();
        }
      }
      return formattedRow;
    });

    res.json({
      success: true,
      data: {
        list: formattedData,
        total: formattedData.length
      }
    });

  } catch (error) {
    console.error('玻璃查询失败:', error);
    // 添加更多调试信息
    console.error('数据库连接信息:', {
      database: 'mesdb',
      query: ``
    });

    res.status(500).json({
      success: false,
      error: error.message,
      stack: error.stack,
      query: ``
    });
  }
});

// 添加组件查询接口
router.get('/db/selectLot/:lotId', async (req, res) => {
  try {
    const { lotId } = req.params;
    const pool = await dbManager.getPool('mesdb');
    const request = pool.request();

    const query = `
      SELECT * 
      FROM dbo.wip_lot 
      WHERE lot_id = @lotId
    `;

    console.log('组件查询参数:', lotId);

    request.input('lotId', sql.NVarChar, lotId);
    const result = await request.query(query);

    console.log('组件查询结果:', result);

    if (!result.recordset || result.recordset.length === 0) {
      return res.json({
        success: true,
        data: {
          list: [],
          total: 0,
          message: '未找到匹配的组件记录'
        }
      });
    }

    // 格式化日期字段
    const formattedData = result.recordset.map(row => {
      const formattedRow = { ...row };
      for (let key in formattedRow) {
        if (formattedRow[key] instanceof Date) {
          formattedRow[key] = formattedRow[key].toLocaleString();
        }
      }
      return formattedRow;
    });

    res.json({
      success: true,
      data: {
        list: formattedData,
        total: formattedData.length
      }
    });

  } catch (error) {
    console.error('组件查询失败:', error);
    res.status(500).json({
      success: false,
      error: error.message,
      stack: error.stack,
      query: `SELECT * FROM wip_lot WHERE lot_id = '${req.params.lotId}'`
    });
  }
});

// 修改玻璃编号查询接口
router.get('/db/selectBattery/:glassId', async (req, res) => {
  try {
    const { glassId } = req.params;
    const pool = await dbManager.getPool('dcdb');
    const request = pool.request();
    
    // 先切换到正确的数据库并查询表名
    const query = `
      USE DC_DB;
      SELECT * 
      FROM dbo.dc_ods_glassbatterynumber
      WHERE glass_number_i_d = @glassId
    `;

    console.log('玻璃ID查询参数:', glassId);
    console.log('执行查询:', query);
    
    request.input('glassId', sql.NVarChar, glassId);
    const result = await request.query(query);

    if (!result.recordset || result.recordset.length === 0) {
      return res.json({
        success: true,
        data: {
          list: [],
          total: 0,
          message: 'MES没有排版机绑定数据'
        }
      });
    }

    const formattedData = result.recordset.map(row => {
      const formattedRow = { ...row };
      for (let key in formattedRow) {
        if (formattedRow[key] instanceof Date) {
          formattedRow[key] = formattedRow[key].toLocaleString();
        }
      }
      return formattedRow;
    });

    res.json({
      success: true,
      data: {
        list: formattedData,
        total: formattedData.length
      }
    });

  } catch (error) {
    console.error('玻璃ID查询失败:', error);
    // 添加更详细的错误信息
    console.error('数据库连接信息:', {
      database: 'dcdb',
      glassId: req.params.glassId,
      currentDatabase: 'USE DC_DB 查询中...'
    });

    // 尝试查询当前数据库和表信息
    try {
      const dbInfoQuery = await request.query(`
        SELECT DB_NAME() as current_db;
        SELECT OBJECT_ID('dbo.dc_ods_glassbatterynumber') as table_exists;
      `);
      console.error('当前数据库信息:', dbInfoQuery);
    } catch (dbError) {
      console.error('数据库信息查询失败:', dbError);
    }

    res.status(500).json({
      success: false,
      error: error.message,
      stack: error.stack
    });
  }
});

module.exports = router;

